package com.vito.base.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import com.vito.anotition.ExcelFiled;
import com.vito.base.exception.ParamErrorException;

/**
 * excel 读取类   通过readExcel("E:/user.xls",0);
 * */
public class ExcelHelper {
	public enum ReadType{
		LIST,MAP,BEAN
	}
	
	public static interface BeanFiled<T>{
		public T deal(Map<String, String> rowMap);
	}
	private Workbook workbook = null;
	private String description = null;
	public String getDescription() {
		return description;
	}
	
	public Workbook getWorkbook() {
		return workbook;
	}
	/**
	 * @param path 绝对路径
	 * @param sheet 读取excel的第几页
	 * @throws IOException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * */
	public <T> List<Object> read(String path,int sheet,BeanFiled<T> bf,ReadType type) throws IOException,ParamErrorException {
		if (path == null || "".equals(path)) {
			throw new ParamErrorException("filepath is null or empty string");
		} else {
			String postfix = StringUtil.getExt(path);
			InputStream is = null;
			HttpClient hcClient = null;
			try{
				if(path.startsWith("http://")){
					hcClient = new HttpClient(path);
					is = hcClient.getInputStream();
				}else{
					if(path.startsWith("file://")){
						path = path.substring(7);
					}			
					is = new FileInputStream(path);
				}
				try{
					workbook = new XMLSaxSSFWorkbook(is);
					this.description = ((XMLSaxSSFWorkbook)workbook).getDescription();
				}catch(Exception e){
					if(path.startsWith("http://")){
						hcClient = new HttpClient(path);
						is = hcClient.getInputStream();
					}else{
						if(path.startsWith("file://")){
							path = path.substring(7);
						}			
						is = new FileInputStream(path);
					}
					if (postfix.endsWith("xls")) {
						workbook = new HSSFWorkbook(is);
					} else if (postfix.endsWith("xlsx")) {
						workbook = new XSSFWorkbook(is);
					} else {
						throw new ParamErrorException("文件后缀名不是.xls 或 .xlsx");
					}
					HSSFWorkbook book = (HSSFWorkbook) workbook;
					SummaryInformation sumInfo = book.getSummaryInformation();
					this.description= sumInfo.getComments();
				}
				if(type==null||type.equals(ReadType.MAP)){
					return readExcelByMap(workbook,sheet,bf);
				}else if(type.equals(ReadType.LIST)){
					return readExcelByList(workbook,sheet,bf);
				}else
					return null;
			} finally {
				try{
					if(is!=null){
						is.close();
					}
				}finally{
					if(hcClient!=null){
						hcClient.close();
					}
				}
			}
		}
	}
	

	private <T> List<Object> readExcelByMap(Workbook workbook,int numSheet,BeanFiled<T> bf) {
			List<Object> rowList = null;
			rowList = new ArrayList<Object>();
			Sheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				return null;
			}
			List<String> colName = null;
			int collength = 0;
			Map<String,String> rowMap = null;
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if(rowNum == 1){
					colName = new ArrayList<String>();
					for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
						colName.add(getValue(row.getCell(cellNum)));
					}
					collength = colName.size();
				}else{
					if (row != null) {
						rowMap = new HashMap<String,String>();
						for (int cellNum = 0; cellNum < collength; cellNum++) {
							rowMap.put(colName.get(cellNum), getValue(row.getCell(cellNum)));
						}
						if(bf!=null) rowList.add(bf.deal(rowMap));
						else rowList.add(rowMap);
					}
				}
			}
			return rowList;
	}

	private <T> List<Object> readExcelByList(Workbook workbook,int numSheet,BeanFiled<T> bf) {
		List<Object> rowList = new ArrayList<Object>();
		Sheet sheet = workbook.getSheetAt(numSheet);
		if (sheet == null) {
			return null;
		}
		int collength = 0;
		List<String> rowMap = null;
		for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (row != null) {
				rowMap = new ArrayList<String>();
				collength = row.getLastCellNum();
				for (int cellNum = 0; cellNum < collength; cellNum++) {
					boolean isMerge = isMergedRegion(sheet, rowNum, cellNum);  
					//判断是否具有合并单元格  
					if(isMerge) {  
						String rs = getMergedRegionValue(sheet, row.getRowNum(), cellNum);
						rowMap.add(rs);
					}else {  
						rowMap.add(getValue(row.getCell(cellNum)));
					}
				}
				rowList.add(rowMap);
			}else{
				rowList.add(null);
			}
		}
		return rowList;
	}

	 
	/**   
	* 获取合并单元格的值   
	* @param sheet   
	* @param row   
	* @param column   
	* @return   
	*/    
	public String getMergedRegionValue(Sheet sheet ,int row , int column){    
	    int sheetMergeCount = sheet.getNumMergedRegions();    
	        
	    for(int i = 0 ; i < sheetMergeCount ; i++){    
	        CellRangeAddress ca = sheet.getMergedRegion(i);    
	        int firstColumn = ca.getFirstColumn();    
	        int lastColumn = ca.getLastColumn();    
	        int firstRow = ca.getFirstRow();    
	        int lastRow = ca.getLastRow();    
	            
	        if(row >= firstRow && row <= lastRow){    
	                
	            if(column >= firstColumn && column <= lastColumn){    
	                Row fRow = sheet.getRow(firstRow);    
	                Cell fCell = fRow.getCell(firstColumn);    
	                return getValue(fCell) ;    
	            }    
	        }    
	    }    
	        
	    return null ;    
	}    
	  
	/**  
	* 判断指定的单元格是否是合并单元格  
	* @param sheet   
	* @param row 行下标  
	* @param column 列下标  
	* @return  
	*/  
	private boolean isMergedRegion(Sheet sheet,int row ,int column) {  
	  int sheetMergeCount = sheet.getNumMergedRegions();  
	  for (int i = 0; i < sheetMergeCount; i++) {  
		CellRangeAddress range = sheet.getMergedRegion(i);  
		int firstColumn = range.getFirstColumn();  
		int lastColumn = range.getLastColumn();  
		int firstRow = range.getFirstRow();  
		int lastRow = range.getLastRow();  
		if(row >= firstRow && row <= lastRow){  
			if(column >= firstColumn && column <= lastColumn){  
			return true;  
			}  
		}  
	  }  
	  return false;  
	}  
	  
	private String getValue(Cell cell) {
		if(cell==null) return "";
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				String value = String.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()));
				if(value.endsWith("00:00:00")){
					value = String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
				}
				return value;
			}else{
				try {  
					//return String.valueOf(new DecimalFormat("#.##").format(cell.getNumericCellValue()));
					return String.valueOf(cell.getNumericCellValue());
				} catch (IllegalStateException e) {  
					return String.valueOf(cell.getNumericCellValue());  
				}  
			}
		}  else {
			try {  
				return String.valueOf(cell.getStringCellValue());  
			} catch (IllegalStateException e) {  
				return String.valueOf(cell.getNumericCellValue());  
			}  
		}
	}
	/**
	 * 将excel 内容读取为List<Map>集合
	 * startrow 1
	 * */
	public List<Object> readMap(String path) throws Exception {
		return read(path,0,null,ReadType.MAP);
	}
	
	/**
	 * 将excel 内容读取为List<List> 集合
	 * @throws ParamErrorException 
	 * @throws IOException 
	 * startrow 0
	 * */
	public List<Object> readList(String path) throws IOException, ParamErrorException{
		return read(path,0,null,ReadType.LIST);
	}
	
	/**
	 * 将excel 内容读取为Bean 忽略大小写
	 * */
	@SuppressWarnings("unchecked")
	public <T> List<T> readBean(String path, final Class<T> clazz) throws Exception {
		List<Object> list = read(path,0,new BeanFiled<T>() {
			@Override
			public T deal(Map<String, String> map) {
				try {
					String value;
					T t = clazz.newInstance();
					Field[] fs = clazz.getDeclaredFields();
					for(Field f:fs){
						f.setAccessible(true);
						ExcelFiled an = null;
						if((an = f.getAnnotation(ExcelFiled.class))!=null){
							if(!an.disable()){
								value = an.value();
								for(String key:map.keySet()){
									if(value.toLowerCase().equals(key==null?"":key.toLowerCase())){
										f.set(t, map.get(key));
										break;
									}
								}
							}
						}
					}
					return t;
				} catch (Exception e) {
					e.printStackTrace();
					return null;
				}
			}
		},ReadType.MAP);
		return (List<T>) list;
	}

	/**
	 * 将excel 内容读取内Map 并通过bf接口将map转换为需要的类型数据
	 * */
	@SuppressWarnings("unchecked")
	public <T> List<T> readBean(String path, BeanFiled<T> bf) throws Exception {
		if(bf==null)
			throw new ParamErrorException("BeanFiled 实现类必须存在！");
		return (List<T>) read(path,0,bf,ReadType.MAP);
	}

}



